import PySimpleGUI as sg
import re
from sqlhlper import SQLHLPER
from datetime import datetime,date

def testsql():
    rs=SQLHLPER.selectall('select * from dayoff where id=100')
    print(rs)


def listbox():
    layout=[[sg.Input('',size=(10,1)),sg.Listbox([1234455,23456,223567],key='-a-',size=(3,10),select_mode=sg.LISTBOX_SELECT_MODE_EXTENDED),sg.Button('print')]]
    window=sg.Window('test listbox',layout)
    event,values=window.Read()
    if event=='print':
        print(event,values['-a-'][0])
        print(sg.PopupGetText('groupname') is None)


def combo():
    layout=[[sg.Input('',size=(10,1)),sg.Combo([1234455,23456,223567],key='-a-',readonly=True),sg.Button('print')]]
    window=sg.Window('test combo',layout)
    event,values=window.Read()
    if event=='print':
        print(event,values['-a-'])



def radio():
    layout=[[sg.Radio('  a   ',group_id='-g-',key='-a-',enable_events=True),sg.Radio('  b   ',group_id='-g-',key='-b-',enable_events=True)]]
    window=sg.Window('test radio',layout)
    event,values=window.Read()
    print(event,values['-a-'])



def filing():

    sqlstr="select id,DIS_GROUP,DOCTOR,date(dis_date),date(filing_time),JULIANDAY(strftime('%Y-%m-%d',filing_time))-JULIANDAY(DATE(DIS_DATE)) from mrinfo where date(filing_time)>date('2020-06-01') and date(filing_time)<date('2020-06-30')"
    rs_mr=SQLHLPER.selectall(sqlstr)
    # print(rs_mr)
    mr_list=[list(item) for item in rs_mr]
    print(mr_list)
    SQLHLPER.execute("delete from tempfilingday")
    for item in mr_list:
        # sqlstr="select count(offday) from dayoff where date(offday)>=? and date(offday)<=?"
        # dayoffs_count=SQLHLPER.selectone(sqlstr,*(datetime.strptime(item[1],'%Y-%m-%d').date(),datetime.strptime(item[2],'%Y-%m-%d').date()))
        # dayoff_nums=SQLHLPER.selectall()
        sqlstr="select count(offday) from dayoff where date(offday)>=? and date(offday)<=? "
        dayoffs_count=SQLHLPER.selectone(sqlstr,*(item[3],item[4]))

        item.append(dayoffs_count[0])

        insert_sql="insert into tempfilingday(mrinfo_id,DIS_GROUP,DOCTOR,filing_days) values(?,?,?,?)"
        rs=SQLHLPER.execute(insert_sql,*(item[0],item[1],item[2],item[5]-item[6]))

    calculator_sql='''
    SELECT mrinfo.DIS_GROUP,count(mrinfo.HOSPITAL_NUM) as mrcount,
    ROUND(workdays2/(count(mrinfo.HOSPITAL_NUM)*1.0)*100,2),
    ROUND(workdays3/(count(mrinfo.HOSPITAL_NUM)*1.0)*100,2),
    ROUND(workdays7/(count(mrinfo.HOSPITAL_NUM)*1.0)*100,2) 
    from (((mrinfo LEFT OUTER JOIN (SELECT DIS_GROUP,COUNT(*) as workdays7 from tempfilingday WHERE FILING_DAYS<=7 GROUP BY DIS_GROUP)tmp7d ON mrinfo.DIS_GROUP=tmp7d.DIS_GROUP)
    LEFT OUTER JOIN (SELECT DIS_GROUP,COUNT(*) as workdays2 from tempfilingday WHERE FILING_DAYS<=2 GROUP BY DIS_GROUP)tmp2d ON mrinfo.DIS_GROUP=tmp2d.DIS_GROUP)
    LEFT OUTER JOIN (SELECT DIS_GROUP,COUNT(*) as workdays3 from tempfilingday WHERE FILING_DAYS<=3 GROUP BY DIS_GROUP)tmp3d ON mrinfo.DIS_GROUP=tmp3d.DIS_GROUP)
    GROUP BY mrinfo.DIS_GROUP ORDER BY mrinfo.DIS_GROUP
    '''
    filingrate=SQLHLPER.selectall(calculator_sql)


    print(filingrate)


def multiplewindow():
    #!/usr/bin/env python
    import PySimpleGUI as sg

    '''
    Parallel windows executing.
    '''

    layout1 = [[ sg.Text('Window 1') ],
            [sg.Input('')],
            [ sg.Button('Read')]]

    window1 = sg.Window('My new window', layout1, location=(800,500))


    layout2 = [[ sg.Text('Window 2') ],
            [sg.Input('')],
            [ sg.Button('Read')]]

    window2 = sg.Window('My new window', layout2, location=(800, 625), return_keyboard_events=True)


    layout3 = [[ sg.Text('Window 3') ],
            [sg.Input(do_not_clear=False)],
            [ sg.Button('Read')]]

    window3 = sg.Window('My new window', layout3, location=(800,750), return_keyboard_events=True)


    while True:     # Event Loop
        event, values = window1.read(timeout=0)
        if event == sg.WIN_CLOSED:
            break
        elif event != '__timeout__':
            print(event, values)

        event, values = window2.read(timeout=0)
        if event == sg.WIN_CLOSED:
            break
        elif event != '__timeout__':
            print(event, values)

        event, values = window3.read(timeout=0)
        if event == sg.WIN_CLOSED:
            break
        elif event != '__timeout__':
            print(event, values)

    window1.close()
    window2.close()
    window3.close()

